﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Windows.Forms;
using System.Runtime.InteropServices;

namespace WoWDeckbuilder
{
    class Database
    {
        // Variable
        private string ConnectionString;
        private SqlCeConnection Connection;
        private SqlCeCommand Command;
        private SqlCeDataReader Reader;
        // Constructor
        public Database()
        {
            ConnectionString = "Data Source=" + Application.ExecutablePath.Substring(0, Application.ExecutablePath.LastIndexOf("\\")) + "\\WoWTCG.sdf;Persist Security Info=False;";
            Connection = new SqlCeConnection(ConnectionString);
        }
        // Methodes
        /// <summary>
        /// Gets the data from the databasee.
        /// </summary>
        /// <param name="Table">The table</param>
        /// <param name="Colum">The column(s)</param>
        /// <param name="Where">The where statement (wihtout WHERE)</param>
        /// <param name="Order">The column it need to be ordered by.</param>
        /// <returns>SqlCeDataReader with all the data.</returns>
        public SqlCeDataReader GetData(string Table, [Optional]string Colum, [Optional]string Where, [Optional]string Order)
        {
            string sql="SELECT ";
            if (Colum == null)
            {
                sql += "* FROM " + Table;
            }
            else
            {
                sql += Colum + " FROM " + Table;
            }
            if (Where != null || Where == "")
            {
                sql += " WHERE " + Where;
            }
            if (Order != null || Where == "")
            {
                sql += " Order By " + Order + " ASC";
            }
            Command = new SqlCeCommand(sql, Connection);
            if (Connection.State == System.Data.ConnectionState.Closed)
                Connection.Open();
            Reader = Command.ExecuteReader();
            return Reader;
        }
        /// <summary>
        /// Closes the connection.
        /// </summary>
        public void Close()
        {
            Connection.Close();
        }
        /// <summary>
        /// Gets one column from the database.
        /// </summary>
        /// <param name="Table">The table</param>
        /// <param name="Colum">The colum</param>
        /// <param name="Where">The where statement (wihtout WHERE)</param>
        /// <returns>List of strings with the data.</returns>
        public List<string> GetColum(string Table, string Colum, [Optional]string Where)
        {
            List<string> Row = new List<string>();
            string sql = "SELECT [" + Colum + "] FROM " + Table;
            if (Connection.State == System.Data.ConnectionState.Open)
                Connection.Close();
            if (Where != null)
            {
                sql += " WHERE " + Where;
            }
            try
            {
                Command = new SqlCeCommand(sql, Connection);
                Connection.Open();
                Reader = Command.ExecuteReader();
                while (Reader.Read())
                {
                    Row.Add(Reader[Colum].ToString());
                }
            }
            catch(Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                Connection.Close();
            }
            return Row;
        }
        /// <summary>
        /// Gets the cards extra information.
        /// </summary>
        /// <param name="Table">The table</param>
        /// <param name="Colum">The column(s)</param>
        /// <param name="Where">The where statement (wihtout WHERE)</param>
        /// <returns>SqlCeDataReader with all the data.</returns>
        public SqlCeDataReader GetCardExtra(string Table, [Optional]string Colum, string Where)
        {
            string sql;
            if (Colum == null || Colum == "")
            {
                sql = "SELECT * FROM " + Table + " WHERE CardNumber='" + Where + "'";
            }
            else
            {
                sql = "SELECT " + Colum + " FROM " + Table + " WHERE " + Where;
            }
            try
            {
                Command = new SqlCeCommand(sql, Connection);
                if (Connection.State == System.Data.ConnectionState.Closed)
                    Connection.Open();
                Reader = Command.ExecuteReader();
            }
            catch
            {
            }
            return Reader;
        }
        /// <summary>
        /// Gets one value from the database.
        /// </summary>
        /// <param name="Table">The table</param>
        /// <param name="Colum">The column</param>
        /// <param name="Where">The where statement (wihtout WHERE)</param>
        /// <returns>String with the value</returns>
        public string GetValue(string Table, string Colum, string Where)
        {
            string val = "";
            string sql = "SELECT " + Colum + " FROM " + Table + " WHERE " + Where;
            try
            {
                Command = new SqlCeCommand(sql, Connection);
                if (Connection.State == System.Data.ConnectionState.Closed)
                    Connection.Open();
                Reader = Command.ExecuteReader();
                Reader.Read();
                val = Reader[0].ToString();
            }
            catch
            {
            }
            finally
            {
                Connection.Close();
            }
            return val;
        }
    }
}
